The present invention relates generally to a database processing system which references files, and more particularly to a technology which is effectively applied to a database processing system that executes definitions and operations for data of file reference type capable of referencing a file so as to improve the integrity of a database (DB).
A database management system (hereinafter abbreviated as the xe2x80x9cDBMSxe2x80x9d), particularly a relational DBMS, processes queries which are expressed in a non-procedural language. The SQL (Structured Query Language) has been employed as this database language. At present, the standardization is under progress for next generation SQL (under ISO, ANSI) in the database language SQL. The reference type is one of main functions implemented in the next generation SQL. The reference type stores a pointer in a particular row in a separate table within a DB, rather than storing a value in a column. In this way, information in a referenced row can be provided as shared data.
In recent years, there has been an increasing need for employing a DBMS to manage multimedia data including texts, graphics, pictures, audio, moving image data and so on which may be accessed through WWW (World Wide Web) or the like. However, these data are generally produced and modified by their individual applications dedicated thereto, so that once data is stored within a DBMS, the data must be retrieved from and stored again in the DBMS when a dedicated application has to handle data produced thereby. To eliminate this inconvenience, an external file function has been devised for equally handling data stored in a file placed external to a DBMS, as if the data were stored in the DBMS, by indicating this external file. This external file function is supported as BFILE type in Oracle8 which is an RDBMS available from ORACLE Corporation. Also, IBM has published a similar external file function called xe2x80x9cDataLinksxe2x80x9d for its DB2 Universal Database (see Search Report, DataLinks: MANAGING EXTERNAL DADA WITH DB2 UNIVERSAL DATABASE, by InfoIT, Inc., pp10-11, August 1997).
As part of core capabilities, conventional DBMS""s maintain the integrity, so that rows of a DB are established together with the existence of all columns, and only values in a certain column will never be missing. When a column, for which an index is defined, is updated, the index must be modified to have the most recent information. Thus, if a conventional DBMS is simply provided with the ability of handling external files, operations such as update, delete and so on will be performed to such external files out of the management of the DBMS. Since the external files are not managed by the DBMS, the operations performed thereon will arise a problem in that the DBMS suffers from difficulties in maintaining the integrity such as that normally maintained within columns of the DBMS.
While a method of indicating an external file out of the management of a DBMS has been contemplated in the aforementioned background art techniques, the BFILE type is dedicated to reading, so that it is not recovered when a fault occurs. Moreover, the BFILE type is not a function created in consideration of an update performed outside a DBMS. DataLinks, in turn, employs a method of controlling external files such that they are not rewritten by external applications or the like which do not have an access right to a DB from which the external files may be accessed, in order to restrict accesses to external files. DataLinks, however, does not consider actions to take in the DBMS itself when an associated external file is updated.
It is therefore an object of the present invention to provide a technology which is capable of improving the integrity of a database by treating data in external files placed outside the database as data equivalent to ordinary column data stored in the database.
Specifically, the present invention provides a database processing system for defining and operating data of external file reference type capable of referencing an external file, wherein external files registered in a database are watched to check whether any file is updated or deleted, and column data within the database is updated corresponding to any processing on the external files.
In the database processing system according to the present invention, when column data is inserted, a watch request is issued in accordance with watch information in the column data for watching an external file indicated by position information in the column data. Then, the position information and the watch information in the column data are stored in the database.
When table select processing is performed, a processing type is examined in a table select request. When the processing type indicates a select of column data, the database processing system sends to a database client column data which meets a select condition. When the processing type indicates a select of the contents of an external file, the database processing system acquires the contents of an external file indicated by position information in column data, which meets the select condition, and transmits the contents to the database client.
Also, when table update processing is performed, a processing type in a table update request is examined. When the processing type indicates an update of column data, the database processing system notifies the termination of a watch on an external file indicated by position information in column data to be updated in accordance with watch information in the column data to be updated. After updating the column data to be updated with position information and watch information in the table update request, the database processing system issues a request for watching an external file indicated by the position information in the table update request in accordance with the watch information in the table update request.
When the processing type in the table update request indicates an update of contents of an external file in the table update processing, the database processing system updates the contents of an external file indicated by the position information in the column data to be updated with the contents of an external file indicted by the position information in the table update request.
When table delete processing is performed, the database processing system notifies the termination of a watch on an external file in accordance with watch information in column data to be deleted. When the processing type indicates a delete of the contents of an external file, the database processing system deletes the external file, and then the column data to be deleted.
When a request for watching an external file is issued in a manner mentioned above, the database processing system registers a watched file list with information on the external file, and initiates watching the external file. If the external file is updated or deleted, the database processing system sends to a watch requester an update notice indicating that the external file has been updated or deleted. When a watch termination notice is transmitted to terminate the watch on the external file, the database processing system deletes information on the external file associated with the watch termination notice from the watched file list.
When an update notice for the external file is sent, the contents of the update notice are examined. When the update notice indicates an update of an external file, the database processing system executes maintenance processing on an index which is registered in column data that indicates the updated external file. On the other hand, when the update notice indicates a delete of an external file, the database processing system deletes a row corresponding to column data that indicates the deleted external file, and executes maintenance processing on an index which is registered in a table.
As described above, in the present invention, the external file reference type is added to data types available in a DBMS, and position information and watch information are entered as data, so that the contents of an external file can be referenced as data linked by specified position information. The integrity of the DBMS can be maintained by watching whether any external file has been updated or deleted at a frequency specified in the watch information, and detecting any external file updated or deleted from another application or the like. It is therefore possible to treat external files as data equivalent to ordinary column data stored in a database without the need for storing the contents of external file in the DBMS.
As is appreciated from the foregoing, the database processing system according to the present invention detects an update or a delete performed on any external file indicated by position information in column data within a database, and updates column data within the database corresponding to processing performed on the external file, so that data within external files placed outside the database can be treated as data equivalent to ordinary column data stored in the database itself without damaging the integrity of the database.